package com.emip.dao.impl;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import com.emip.bean.SiteItemBean;
import com.emip.dao.BaseDAO;
import com.emip.dao.SiteItemDAO;
import com.emip.util.Util;



public class StoreDAO extends BaseDAO {

	@Autowired
	SiteItemDAO siteItemDAO;
	
	//日期类型
	protected String getSecondHQL(String startDate, String endDate, String[] items, String siteId) {
		return getDateHQL("Second", "YYYY-MM-DD HH24:Mi:SS", startDate, endDate, items, siteId);
	}
	
	protected String getHourHQL(String startDate, String endDate, String[] items, String siteId) {
		return getDateHQL("Hour", "YYYY-MM-DD HH24:Mi:SS", startDate+":00:00", endDate+":59:59", items, siteId);
	}
	
	protected String getDayHQL(String startDate, String endDate, String[] items, String siteId) {
		return getDateHQL("Day", "YYYY-MM-DD HH24:Mi:SS", startDate+"00:00:00", endDate+"23:59:59", items, siteId);
	}
	
	protected String getDateHQL(String type, String format, String startDate, String endDate, String[] items, String siteId) {
		return getNoOrderDateHQL(type, format, startDate, endDate, items, siteId) + " order by entity.genTime, entity.site, entity.item";
	}
	
	protected String getNoOrderDateHQL(String type, String format, String startDate, String endDate, String[] items, String siteId) {
		String hql = " from " + type + "StoreBean entity where entity.genTime " +
				">= to_date('" + startDate + "', '" + format + "') and " +
				"entity.genTime <= to_date('" + endDate + "', '" + format + "')";
		return getITSI(hql, items, siteId);
	}
	
	//数字类型
	protected String getWeekHQL(int startYear, int startWeekth, 
			int endYear, int endWeekth, String[] items, String siteId) {
		return getThHQL("Week", "weekth", startYear, startWeekth, endYear, endWeekth, items, siteId);
	}
	
	protected String getMonthHQL(int startYear, int startMonth, 
			int endYear, int endMonth, String[] items, String siteId) {
		return getThHQL("Month", "month", startYear, startMonth, endYear, endMonth, items, siteId);
	}
	
	protected String getQuarterHQL(int startYear, int startQuarter, 
			int endYear, int endQuarter, String[] items, String siteId) {
		return getThHQL("Quarter", "quater", startYear, startQuarter, endYear, endQuarter, items, siteId);
	}
	
	protected String getYearHQL(int startYear, int endYear, String[] items, String siteId) {
		return getThHQL("Year", null, startYear, 0, endYear, 0, items, siteId);
	}
	
	protected String getThHQL(String type, String th, int startYear, int startth, 
			int endYear, int endth, String[] items, String siteId) {
		String hql = getNoOrderThHQL(type, th, startYear, startth, endYear, endth, items, siteId);
		hql += " order by entity.year";
		if (th != null) {
			hql += ", entity." + th;
		}
		hql += ", entity.site, entity.item";
		return hql;
	}
	
	protected String getNoOrderThHQL(String type, String th, int startYear, int startth, 
			int endYear, int endth, String[] items, String siteId) {
		String hql = " from " + type + "StoreBean entity where entity.year >= " + 
				startYear + " and entity.year <= " + endYear;
		if (th != null) {
			hql += " and TH(entity.year, entity." + th + ") >= " + 
					startYear + (startth < 10 ? "0" + startth : startth) + 
					" and TH(entity.year, entity." + th + ") <= " + 
					endYear + (endth < 10 ? "0" + endth : endth);
		}
		return getITSI(hql, items, siteId);
	}
	
	//通用条件
	protected String getITSI(String hql, String[] items, String siteId) {
		if (siteId != null) {
			hql += " and entity.site.id = '" + siteId + "'";
		}
		if (items != null && items.length > 0) {
			hql += " and entity.item.id in " + Util.Array2String(items);
		}
		return hql;
	}
	
	@SuppressWarnings("unchecked")
	protected <T> List<T> getDatePage(Class<?> T, String format, String startDate,
			String endDate, String[] items, String siteId, int page, int size) {
		SimpleDateFormat df = new SimpleDateFormat(format);
		String qhql = "select distinct entity.genTime " + getDateHQL(T.getSimpleName(), startDate, endDate, items, siteId);
		List<Date> dates = getEntities(Date.class, qhql, page, size);
		if (dates == null || dates.size() == 0) return null;
		startDate = df.format(dates.get(0));
		endDate = df.format(dates.get(dates.size() - 1));
		
		String hql = getDateHQL(T.getSimpleName(), startDate, endDate, items, siteId) + 
				", entity.item.id";
		return getHibernateTemplate().find(hql);
	}
	
	@SuppressWarnings("unchecked")
	protected <T> List<T> getThPage(Class<?> T, String th, int startYear, int startth,
			int endYear, int endth, String[] items, String siteId, int page, int size) {
		String qhql = "select distinct entity.year";
		if (th != null) {
			qhql += ", entity." + th;
		}
		qhql += getThHQL(T.getSimpleName(), startYear, startth, endYear, endth, items, siteId);
		if (th!= null) {
			List<Object[]> dates = getEntities(Object[].class, qhql, page, size);
			if (dates == null || dates.size() == 0) return null;
			startYear = Integer.parseInt(dates.get(0)[0].toString());
			startth = Integer.parseInt(dates.get(0)[1].toString());
			endYear = Integer.parseInt(dates.get(dates.size() - 1)[0].toString());
			endth = Integer.parseInt(dates.get(dates.size() - 1)[1].toString());
		} else {
			List<Object> dates = getEntities(Integer.class, qhql, page, size);
			if (dates == null || dates.size() == 0) return null;
			startYear = Integer.parseInt(dates.get(0).toString());
			endYear = Integer.parseInt(dates.get(dates.size() - 1).toString());
		}
		
		String hql = getThHQL(T.getSimpleName(), startYear, startth, endYear, endth, items, siteId) + 
				", entity.item.id";
		return getHibernateTemplate().find(hql);
		
	}
	
	protected String getDateHQL(String type, String startDate,
			String endDate, String[] items, String siteId) {
		if ("hourstorebean".equalsIgnoreCase(type)) {
			return getHourHQL(startDate, endDate, items, siteId);
		} else if ("secondstorebean".equalsIgnoreCase(type)) {
			return getSecondHQL(startDate, endDate, items, siteId);
		}
			
		return getDayHQL(startDate, endDate, items, siteId);
	}
	
	protected String getThHQL(String type, int startYear, int startth,
			int endYear, int endth, String[] items, String siteId) {
		if ("weekstorebean".equalsIgnoreCase(type)) {
			return getWeekHQL(startYear, startth, endYear, endth, items, siteId);
		} else if ("monthstorebean".equalsIgnoreCase(type)) {
			return getMonthHQL(startYear, startth, endYear, endth, items, siteId);
		} else if ("quarterstorebean".equalsIgnoreCase(type)) {
			return getQuarterHQL(startYear, startth, endYear, endth, items, siteId);
		}
		return getYearHQL(startYear, endYear, items, siteId);
	}

	@SuppressWarnings("unchecked")
	protected List<Object> getNormalDateStat(String type, String format, 
		String startDate, String endDate, String[] items, String siteId, 
		Boolean multi, String groupFormat) {
		Boolean ifPH = false;
		if (items[0] == 2) ifPH = true;
		SiteItemBean siteItem = siteItemDAO.findSiteItem(siteId, items[0]);
		if (siteItem == null) return null;
		String hql = "select max(entity.value) as max,  min(entity.value) as min, " + 
				(ifPH ? "avg" : "avgPH") + 
				"(entity.value) as avg, count(entity.value) as count,";

		if (multi) {
			hql += ", to_char(entity.genTime, '" + groupFormat + "') as genTime ";
		}

		hql += this.getNoOrderDateHQL(type, format, startDate, endDate, items, siteId);
		if (siteItem.geteMaxValue() != 0) {
			hql += " and to_number(entity.value) >= " + siteItem.geteMinValue() + 
					" and to_number(entity.value) <= " + siteItem.geteMaxValue();
		}

		hql += " and (entity.flag is null or entity.flag = '') ";
		
		if (multi) {
			hql += " group by to_char(entity.genTime, '" + groupFormat + "')";

			hql += " order by to_char(entity.genTime, '" + groupFormat + "')";
		}

		return getHibernateTemplate().find(hql);
	}

	@SuppressWarnings("unchecked")
	protected List<Object> getDateStat(String type, String format, 
		String startDate, String endDate, String[] items, String siteId, 
		Boolean multi, String groupFormat) {
		Boolean ifPH = false;
		if (items[0] == 2) ifPH = true;
		SiteItemBean siteItem = siteItemDAO.findSiteItem(siteId, items[0]);
		if (siteItem == null) return null;
		String hql = "select max(entity.value) as max,  min(entity.value) as min, " + 
				(ifPH ? "avg" : "avgPH") + 
				"(entity.value) as avg, count(entity.value) as count,";

		if (multi) {
			hql += ", to_char(entity.genTime, '" + groupFormat + "') as genTime ";
		}

		hql += this.getNoOrderDateHQL(type, format, startDate, endDate, items, siteId);
		
		if (multi) {
			hql += " group by to_char(entity.genTime, '" + groupFormat + "')";

			hql += " order by to_char(entity.genTime, '" + groupFormat + "')";
		}

		return getHibernateTemplate().find(hql);
	}
	
	@SuppressWarnings("unchecked")
	protected Object[] getDateStatistic(String type, String format, 
		String startDate, String endDate, String[] items, String siteId) {
		List<Object> result = getNormalDateStat(type, format, startDate, endDate, items, siteId, false, null);
		Object[] statistic = (Object[]) result.get(0);
		long count = (Long) statistic[3];
		if (count == 0) {
			result = getDateStat(type, format, startDate, endDate, items, siteId, false, null);
			statistic = (Object[]) result.get(0);
			statistic[3] = 0;
		}
		// String[] stats = new String[statistic.length];
		// for (int i = 1; i < statistic.length; i++) {
		// 	stats[i] = statistic[i] == null ? null : statistic[i].toString();
		// }
		// return stats;
		return statistic;
	}

	@SuppressWarnings("unchecked")
	protected List getNormalThStat(String type, String th, int startYear, int startth,
			int endYear, int endth, String[] items, String siteId, Boolean multi) {
		Boolean ifPH = false;
		if (items[0] == 2) ifPH = true;
		SiteItemBean siteItem = siteItemDAO.findSiteItem(siteId, items[0]);
		if (siteItem == null) return null;
		String hql = "select max(entity.value) as max,  min(entity.value) " +
				"as min, " + (ifPH ? "avg" : "avgPH") + 
				"(entity.value) as avg, count(entity.value) as count ";
		
		if (multi) {
			if (th != null) {
				hql += ", TH(entity.year, entity." + th + ") as genTime";
			}
			else {
				hql += ", entity.year as genTime";
			}
		}

		hql += this.getNoOrderThHQL(type, th, startYear, startth, endYear, endth, items, siteId);
		
		hql += " and (entity.flag is null or entity.flag = '') ";
		if (siteItem.geteMaxValue() != 0) {
			hql += " and to_number(entity.value) >= " + siteItem.geteMinValue() + 
					" and to_number(entity.value) <= " + siteItem.geteMaxValue();
		}

		if (multi) {
			if (th != null) {
				hql += " group by TH(entity.year, entity." + th + ")";

				hql += " order by TH(entity.year, entity." + th + ")";
			}
			else {
				hql += "group by entity.year order by entity.year";
			}
		}
		return getHibernateTemplate().find(hql);
	}

	@SuppressWarnings("unchecked")
	protected List getThStat(String type, String th, int startYear, int startth,
			int endYear, int endth, String[] items, String siteId, Boolean multi) {
		Boolean ifPH = false;
		if (items[0] == 2) ifPH = true;
		SiteItemBean siteItem = siteItemDAO.findSiteItem(siteId, items[0]);
		if (siteItem == null) return null;
		String hql = "select max(entity.value) as max,  min(entity.value) " +
				"as min, " + (ifPH ? "avg" : "avgPH") + 
				"(entity.value) as avg, count(entity.value) as count ";
		if (multi) {
			if (th != null) {
				hql += ", TH(entity.year, entity." + th + ") as genTime";
			}
			else {
				hql += ", entity.year as genTime";
			}
		}
		hql += this.getNoOrderThHQL(type, th, startYear, startth, endYear, endth, items, siteId);

		if (multi) {
			if (th != null) {
				hql += " group by TH(entity.year, entity." + th + ")";

				hql += " order by TH(entity.year, entity." + th + ")";
			}
			else {
				hql += "group by entity.year order by entity.year";
			}
		}
		return getHibernateTemplate().find(hql);
	}
	
	@SuppressWarnings("unchecked")
	protected Object[] getThStatistic(String type, String th, int startYear, int startth,
			int endYear, int endth, String[] items, String siteId) {
		
		List<Object> result = getNormalThStat(type, th, startYear, startth, endYear, endth, items, siteId, false);
		Object[] statistic = (Object[]) result.get(0);
		long count = (Long) statistic[3];
		if (count == 0) {
			result = getThStat(type, th, startYear, startth, endYear, endth, items, siteId, false);
			statistic = (Object[]) result.get(0);
		}
		// String[] stats = new String[statistic.length];
		// for (int i = 0; i < statistic.length; i++) {
		// 	stats[i] = statistic[i] == null ? null : statistic[i].toString();
		// }
		return statistic;
	}
	
}
